package com.rybbaby.tss.front.tssMemberAttendClassRecord.dao.imp;

import com.rybbaby.tss.core.bean.PageBean;
import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.front.tssMemberAttendClassRecord.dao.ITssMemberAttendClassRecordDao;
import com.rybbaby.tss.front.tssMemberAttendClassRecord.entity.TssMemberAttendClassRecord;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author 雷春琼
 * @description 会员报班记录表数据库操作实现
 * @createDate 2017-08-24 下午2:43:28
 */
@Repository
public class TssMemberAttendClassRecordDaoImp extends GenericDao<TssMemberAttendClassRecord> implements ITssMemberAttendClassRecordDao {


    /**
     * 获取该会员id下指定报班记录
     * @param lessonIds
     * @param memberid
     * @return
     */
    public List<TssMemberAttendClassRecord> getAttendClassListBylessonIdsAndMemberId(String lessonIds,String memberid){
        String sql = "SELECT * FROM tss_member_attend_class_record WHERE lessonsId IN ('"+lessonIds+"') AND memberId = '"+memberid+"' and status=1";
        return this.findListBySql(sql,TssMemberAttendClassRecord.class,null);
    }



    /**
     * 根据班级id级会员课时包Id查询已报班未未考勤的课节id,及报班id
     * @param targetdataId
     * @param memberPackageId
     * @return
     */
    public   List<Map<String,Object>> getChangeClassLessons(String targetdataId,String memberPackageId){
        String sql = "SELECT \n" +
                "\tlessonsId,id \n" +
                "FROM \n" +
                "\ttss_member_attend_class_record \n" +
                "WHERE \n" +
                "\tid IN ( \n" +
                "\t\tSELECT \n" +
                "\t\t\tattendClassId \n" +
                "\t\tFROM \n" +
                "\t\t\ttss_member_check_attendance \n" +
                "\t\tWHERE \n" +
                "\t\t\tmemberId = ( \n" +
                "\t\t\t\tSELECT \n" +
                "\t\t\t\t\tmemberId \n" +
                "\t\t\t\tFROM \n" +
                "\t\t\t\t\ttss_member_package \n" +
                "\t\t\t\tWHERE \n" +
                "\t\t\t\t\tid=? \n" +
                "\t\t\t\tAND classId=? \n" +
                "\t\t\t\tAND \n" +
                "\t\t\t\t\tattendenceStatus = 0 \n" +
                "\t\t\t\tAND isDelete = 0 \n" +
                "\t\t\t) \n" +
                "\t)";
        return this.findForJdbc(sql, new Object[]{memberPackageId, targetdataId});

    }


    /**
     * 根据会员课时包和班级id查询已报班信息
     *
     * @param memberPackageId
     * @param classId
     * @return
     */
    @Override
    public List<Map<String, Object>> classDateList(String memberPackageId, String classId) {
        String sql = "SELECT\n" +
                "\ta.classDate\n" +
                "FROM\n" +
                "\ttss_member_attend_class_record a\n" +
                "LEFT JOIN tss_member_package p ON p.id = a.memberPackageId\n" +
                "WHERE p.id =?  AND classId = ?";
        return this.findForJdbc(sql, new Object[]{memberPackageId, classId});
    }

    /**
     * 根据会员课时包id分组查询会员报班记录
     *
     * @param memberPackageId
     * @return
     */
    @Override
    public PageBean tssMemberAttendClassRecordListGroupByClass(String memberPackageId, Integer pageNo, Integer pageSize) {
        String sql = "SELECT\n" +
                "\tc.id classId,\n" +
                "\tc.weekDay,\n" +
                "\tc.startTime,\n" +
                "\tc.endTime,\n" +
                "\tcr. NAME roomName,\n" +
                "\tts. realName realName,\n" +
                "\tcs. NAME courseName,\n" +
                "\tcss. NAME systemPidName,\n" +
                "\ta.memberId,\n" +
                "\tc.expendClassHour,\n" +
                "a.memberPackageId,\n" +
                "(select count(1) from tss_member_check_attendance  where classId = c.id and memberId=a.memberId and attendenceStatus=0 and isDelete=0 \n" +
                ")  surplusMayTurnClassHour\n" +
                "FROM\n" +
                "\ttss_classes c\n" +
                "LEFT JOIN tss_class_lessons cl ON cl.classId = c.id\n" +
                "LEFT JOIN tss_member_attend_class_record a ON a.lessonsId = cl.id\n" +
                "LEFT JOIN tss_course tc ON tc.id = c.courseId\n" +
                "LEFT JOIN tss_class_room cr ON cr.id = c.roomId\n" +
                "LEFT JOIN sys_admin_user ts ON ts.id = c.adminUserId\n" +
                "LEFT JOIN sys_course_system cs ON cs.id = tc.systemId\n" +
                "LEFT JOIN sys_course_system css ON css.id = cs.pId\n" +
                "WHERE" +
                "\ta.memberPackageId = '" + memberPackageId + "' AND a.status = 1 AND a.isAttendClass = 0\n" +
                "GROUP BY\n" +
                "\tclassId";
        return this.pageList(sql, pageNo, pageSize);
    }

    @Override
    public List<Map<String, Object>> getMemberAttendClassDetailList(String memberId, String memberPackageId, String classId) {
        String sql = "SELECT\n" +
                "cl.id,cl.classTime,cl.weekDay,cl.classIndexTime,a.isAttendClass,a.isReturnSection,tc.startTime,tc.endTime,(SELECT realName FROM sys_admin_user WHERE id = cl.adminUserId) AS teacherName,tc.expendClassHour,(SELECT name FROM tss_class_room WHERE id = cl.roomId) AS className \n" +
                "FROM \n" +
                "tss_member_attend_class_record a \n" +
                "LEFT JOIN tss_class_lessons cl ON cl.id = a.lessonsId \n" +
                "LEFT JOIN tss_classes tc ON tc.id = cl.classId \n" +
                "LEFT JOIN tss_member_check_attendance ca ON ca.attendClassId = a.id  \n" +
                "WHERE \n" +
                "ca.attendenceStatus= 0 \n" +
                "AND ca.isDelete = 0 \n" +
                "AND a.memberId = ? \n" +
                "AND a.memberPackageId = ? \n" +
                "AND tc.id = ? ORDER BY cl.classTime ASC";
        return this.findForJdbc(sql, new Object[]{memberId, memberPackageId, classId});
    }

    @Override
    public List<Map<String, Object>> getAttendClassLessonsDetailList(String memberId, String classId) {
        String sql = "SELECT\n" +
                "\tcl.id,\n" +
                "\tcl.classTime,\n" +
                "\tcl.weekDay,\n" +
                "\ttc.startTime,\n" +
                "\ttc.endTime,\n" +
                "\ts. realName realName,\n" +
                "\tcr. NAME roomName,\n" +
                "\ta.`status`,\n" +
                "\ta.createTime,\n" +
                "\te.userName,\n" +
                "\t  tc.expendClassHour\n" +
                "FROM\n" +
                " tss_class_lessons cl \n" +
                "LEFT JOIN tss_member_attend_class_record a on a.lessonsId=cl.id\n" +
                "LEFT JOIN tss_classes tc ON tc.id = cl.classId\n" +
                "LEFT JOIN sys_admin_user s ON s.id = tc.adminUserId\n" +
                "LEFT JOIN tss_class_room cr ON cr.id = tc.roomId\n" +
                "LEFT JOIN tss_member_package p ON p.id = a.memberPackageId\n" +
                "LEFT JOIN sys_admin_user e ON e.id = p.adminUserId\n" +
                "WHERE\n" +
                " tc.id = ?\n" +
                "and a.memberId=? \n" +
                "GROUP BY\n" +
                "\tcl.id ";
        return this.findForJdbc(sql, new Object[]{classId, memberId});
    }

    @Override
    public Integer updateNoClassLessionChangealreadyClass(Integer status, String memberId, String memberPackageId, String targetDataId) {
        String[] lessonIds = targetDataId.split(",");
        Integer row = 0;
        for (String lessonId : lessonIds) {
            String sql = "UPDATE tss_member_attend_class_record SET status = ?  WHERE  isAttendClass = 0  AND memberId =?  AND memberPackageId =? and lessonsId=?";
            row = this.executeSql(sql, new Object[]{status, memberId, memberPackageId, lessonId});
        }
        return row;
    }

    @Override
    public Map<String, Object> findByLessonsId(String s1, Date classTime, String memberId) {
        String sql = "select id from tss_member_attend_class_record where lessonsId = ? and createTime = ? and memberId = ?";
        return this.findOneForJdbc(sql, new Object[]{s1, classTime, memberId});
    }

    @Override
    public List<TssMemberAttendClassRecord> findAttendClassRecordByLessonsIdMemberId(String memberId, String id) throws Exception {
        String sql = "select * from tss_member_attend_class_record where lessonsId = '" + id + "' and memberId = '" + memberId + "'";
        return this.findListBySql(sql, TssMemberAttendClassRecord.class, null);
    }

    @Override
    public List<Map<String, Object>> findAttendClassByClassIdAndMemberPackageId(String memberPackageId, String classId) {

        String sql = "select tcl.id from tss_member_attend_class_record acr LEFT JOIN tss_class_lessons tcl ON tcl.id=acr.lessonsId \n" +
                " where acr.memberPackageId=? \n" +
                "and tcl.classId=? ";
        return this.findForJdbc(sql, new Object[]{memberPackageId, classId});
    }

    @Override
    public List<Map<String, Object>> findCouldClassLessonInfoByClassIdAndMemberPackageId(String memberPackageId, String classId) {
        /*String sql = "select tcl.*,ts.realName realName, tcr.name roomName,tc.expendClassHour  from tss_class_lessons tcl\n" +
                "LEFT JOIN tss_classes tc on tc.id=tcl.classId \n" +
                "LEFT JOIN sys_admin_user ts on ts.id= tcl.adminUserId \n" +
                "LEFT JOIN tss_class_room  tcr on tcr.id=tcl.roomId \n" +
                "where tcl.id  not in(?) and tcl.classId=?  order by tcl.classIndexTime asc \n";*/
        String sql = "SELECT \n" +
                "\t*,(SELECT realName FROM sys_admin_user WHERE id=adminUserId) AS realName, \n" +
                "\t(SELECT name FROM tss_class_room WHERE id=roomId) AS roomName, \n" +
                "\t(SELECT expendClassHour FROM tss_classes WHERE id = classId) AS expendClassHour \n" +
                "FROM \n" +
                "\ttss_class_lessons \n" +
                "WHERE \n" +
                "\tclassId =? \n" +
                "AND id NOT IN ( \n" +
                "\tSELECT \n" +
                "\t\tlessonsId \n" +
                "\tFROM \n" +
                "\t\ttss_member_attend_class_record \n" +
                "\tWHERE \n" +
                "\t\tmemberId = ( \n" +
                "\t\t\tSELECT \n" +
                "\t\t\t\tmemberId \n" +
                "\t\t\tFROM \n" +
                "\t\t\t\ttss_member_package \n" +
                "\t\t\tWHERE \n" +
                "\t\t\t\tid =? \n" +
                "\t\t) AND  status=1 \n" +
                ") ORDER BY classIndexTime ASC \n";

        return this.findForJdbc(sql, new Object[]{classId,memberPackageId});
    }

    @Override
    public List<Map<String, Object>> findCouldClassLessonInfoByClassIdAndMemberPackageId(String classId) {
        String sql = "select tcl.*,ts.realName realName, tcr.name roomName,tc.expendClassHour  from tss_class_lessons tcl\n" +
                "LEFT JOIN tss_classes tc on tc.id=tcl.classId \n" +
                "LEFT JOIN sys_admin_user ts on ts.id= tcl.adminUserId \n" +
                "LEFT JOIN tss_class_room  tcr on tcr.id=tcl.roomId \n" +
                "where tcl.classId=? order by tcl.classIndexTime asc\n";
        return this.findForJdbc(sql, new Object[]{classId});
    }

    @Override
    public List<Map<String, Object>> getAttendClassDetailList(String memberId, Integer type) {
        String sql = "select tc.id,cl.id lessonId, tc.name,cs.name systemName from tss_member_attend_class_record   a\n" +
                "LEFT JOIN tss_class_lessons cl ON cl.id = a.lessonsId\n" +
                "LEFT JOIN tss_classes tc ON tc.id = cl.classId\n" +
                "LEFT JOIN tss_course c on c.id=tc.courseId\n" +
                "LEFT JOIN sys_course_system cs on cs.id=c.systemId\n" +
                "left JOIN tss_member_package mp on mp.id=a.memberPackageId\n" +
                "where a.memberId=?\n" +
                "and mp.type=?\n" +
                "GROUP BY tc.id  order by cl.classIndexTime asc";
        return this.findForJdbc(sql, new Object[]{memberId, type});
    }
}
